/*
Date: October 2025
Project: Income and Child Maltreatment: Evidence from a Discontinuity in Tax Benefits
Author: Katherine Rittenhouse
Purpose: This file takes in child-referral-allegation level data through the end of 2020, and creates files at the child-referral level, the referral level and the child level.
Files in: ucb_ref
Files out: CPS_RefID; CPS_ID; CPS_Ref; CPS_1stRef
*/


clear all
set more off


*** cps cleaning 
use "ucb_ref.dta",clear

gen byte age_at_referral = floor((REF_RCV_DT - BIRTH_DT)/365)

gen byte alleg_physical = (ALG_TPC == 2179)

gen byte alleg_neglect = (ALG_TPC == 2178)

gen byte alleg_emotional = (ALG_TPC == 2176)

gen byte alleg_other = 0
replace alleg_other = 1 if ALG_TPC == 5001
replace alleg_other = 1 if ALG_TPC == 2177
replace alleg_other = 1 if ALG_TPC == 2180
replace alleg_other = 1 if ALG_TPC == 2169
replace alleg_other = 1 if ALG_TPC == 2181

foreach cat in "physical" "neglect" "emotional" "other" {
	gen byte subst_`cat' = (alleg_`cat' == 1 & ALG_DSPC == 45)
	gen byte indic_`cat' = (alleg_`cat' == 1 & ALG_DSPC == 47)
}

collapse (max) alleg_* subst_* indic_*,by(FKCLIENT_T FKREFERL_T REF_RCV_DT age_at_referral GENDER ethnic HISP_CD HISP_CDX CENS_RC investigate BIRTH_DT COL_RELC)

gen cps_birthy = year(BIRTH_DT)
gen cps_birthm = month(BIRTH_DT)
gen cps_birthd=day(BIRTH_DT)

duplicates tag FKCLIENT_T FKREF,gen(duptag)
drop if duptag==1 
drop duptag
//550

drop if age_at_referral<0
keep if age_at_referral<18
ren FKCLIENT_T fkclient_t
save "CPS_RefID",replace

gen byte numreferrals = 1
forval x = 0/17 {
	gen byte numrefage`x' = age_at_referral==`x'	
	foreach cat in "physical" "neglect" "emotional" "other" {
	gen byte numal`cat'_`x'= age_at_referral==`x'	& alleg_`cat'==1
	gen byte numsu`cat'_`x'= age_at_referral==`x'	& subst_`cat'==1
	gen byte numin`cat'_`x' = age_at_referral==`x' & indic_`cat'==1
	}
}


gen refcat = "other"
*nonmand - aunt, brother, cousin, grandparent, great aunt/uncle, great grandparent, relative, sister, uncle, friend, neighbor, godparent, landlord, no relation
replace refcat = "nonmand" if COL_RELC == 572
replace refcat = "nonmand" if COL_RELC == 573
replace refcat = "nonmand" if COL_RELC == 577
replace refcat = "nonmand" if COL_RELC == 583
replace refcat = "nonmand" if COL_RELC == 584
replace refcat = "nonmand" if COL_RELC == 585
replace refcat = "nonmand" if COL_RELC == 586
replace refcat = "nonmand" if COL_RELC == 596
replace refcat = "nonmand" if COL_RELC == 598
replace refcat = "nonmand" if COL_RELC == 601
replace refcat = "nonmand" if COL_RELC ==580
replace refcat = "nonmand" if COL_RELC ==591
replace refcat = "nonmand" if COL_RELC ==581
replace refcat = "nonmand" if COL_RELC ==588
replace refcat = "nonmand" if COL_RELC ==592

*school - school personnel, teacher, 
replace refcat = "school" if COL_RELC == 597
replace refcat = "school" if COL_RELC == 600


*medical professional - doctor, counselor, dentist
replace refcat = "medprof" if COL_RELC ==590
replace refcat = "medprof" if COL_RELC ==576
replace refcat = "medprof" if COL_RELC ==5924


*legal professional - law enforcement, parole officer, probation officer
replace refcat = "legprof" if COL_RELC ==589
replace refcat = "legprof" if COL_RELC ==595
replace refcat = "legprof" if COL_RELC ==594

*cws staff
replace refcat = "cws" if COL_RELC ==578



foreach cat in "nonmand" "school" "medprof" "legprof" "other" "cws" {
	forval x = 0/17 {
		gen byte numrep`cat'age`x' = age_at_referral==`x' & refcat=="`cat'"
	}
}
keep alleg_* subst_* indic_* age_at_referral numreferrals numrefage* numrep* numal* numsu* numin* fkclient_t cps_birth* ethnic GENDER HISP* CENS


collapse (max) alleg_* subst_* indic_* (min) age_at_referral (sum) numreferrals numrefage* numrep* numal* numsu* numin*,by(fkclient_t cps_birth* ethnic GENDER HISP* CENS)

ren age minage
save "CPS_ID",replace


use "CPS_RefID",clear 

gen numchildren = 1
collapse (max) alleg_* subst_* indic_* (min) age_at_referral (sum) numchildren ,by(fkclient_t)

ren age minage
save "CPS_Ref",replace

*first referral only
use "CPS_RefID",clear 

gen refcat = "other"
*nonmand - aunt, brother, cousin, grandparent, great aunt/uncle, great grandparent, relative, sister, uncle, friend, neighbor, godparent, landlord, no relation
replace refcat = "nonmand" if COL_RELC == 572
replace refcat = "nonmand" if COL_RELC == 573
replace refcat = "nonmand" if COL_RELC == 577
replace refcat = "nonmand" if COL_RELC == 583
replace refcat = "nonmand" if COL_RELC == 584
replace refcat = "nonmand" if COL_RELC == 585
replace refcat = "nonmand" if COL_RELC == 586
replace refcat = "nonmand" if COL_RELC == 596
replace refcat = "nonmand" if COL_RELC == 598
replace refcat = "nonmand" if COL_RELC == 601
replace refcat = "nonmand" if COL_RELC ==580
replace refcat = "nonmand" if COL_RELC ==591
replace refcat = "nonmand" if COL_RELC ==581
replace refcat = "nonmand" if COL_RELC ==588
replace refcat = "nonmand" if COL_RELC ==592

*school - school personnel, teacher, 
replace refcat = "school" if COL_RELC == 597
replace refcat = "school" if COL_RELC == 600


*medical professional - doctor, counselor, dentist
replace refcat = "medprof" if COL_RELC ==590
replace refcat = "medprof" if COL_RELC ==576
replace refcat = "medprof" if COL_RELC ==5924


*legal professional - law enforcement, parole officer, probation officer
replace refcat = "legprof" if COL_RELC ==589
replace refcat = "legprof" if COL_RELC ==595
replace refcat = "legprof" if COL_RELC ==594

*cws staff
replace refcat = "cws" if COL_RELC ==578

sort fkclient REF_RCV_DT
by fkclient: gen refnum=_n

keep if refnum==1
ren (refcat alleg_* investigate age_at_referral) (refcat1st alleg_*1st investigate1st age1st)

keep fkclient *1st
save "CPS_1stRef",replace
